<?php
class Job extends AppModel {
	var $name = "Job";
	var $useTable = "job_mst";
	var $primaryKey = 'id';

	var $belongsTo = array (
		'Company' => array (
			'className' => 'Company',
			'conditions' => "",
			'order' => '',
			'foreignKey' => 'company_id'
		),
		'CompanyAccount' =>  array(
            'className'  => 'CompanyAccount', 
            'conditions' => "",  
            'order'      => '',  
            'foreignKey' => 'admin_id'  
            )
	);

	var $hasMany = array (
		'JobArea' => array (
			'className' => 'JobArea',
			'conditions' => '"JobArea"."deleted" is null',
			'foreignKey' => 'job_id',
			'dependent' => true,
			'exclusive' => false
		),
		'JobCategory' => array (
			'className' => 'JobCategory',
			'conditions' => '"JobCategory".deleted is null',
			'foreignKey' => 'job_id',
			'dependent' => true,
			'exclusive' => false
		),
		'JobHopeLanguage' => array (
			'className' => 'JobHopeLanguage',
			'conditions' => '"JobHopeLanguage".deleted is null',
			'foreignKey' => 'job_id',
			'dependent' => true,
			'exclusive' => false
		),
		'JobHopeMember' => array (
			'className' => 'JobHopeMember',
			'conditions' => '"JobHopeMember".deleted is null',
			'foreignKey' => 'job_id',
			'dependent' => true,
			'exclusive' => false
		),
		'JobPost' => array (
			'className' => 'JobPost',
			'conditions' => '"JobPost".deleted is null',
			'foreignKey' => 'job_id',
			'dependent' => true,
			'exclusive' => false
		),
		'JobAttention' => array (
			'className' => 'JobAttention',
			'conditions' => '"JobAttention".deleted is null',
			'foreignKey' => 'job_id',
			'dependent' => true,
			'exclusive' => false
		)
	);

	function valid($Valid, $params) {
		$message = '';
		$message .= $Valid->ErrorMsg($params, 'Job/work_type', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/work_type','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params, 'Job/post_type', 'required');
		$message .= $Valid->ErrorMsg($params, 'Job/content', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/content','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params, 'Job/condition', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/condition','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params, 'Job/salary', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/salary','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params, 'Job/start_date', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/start_date','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params, 'Job/location', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/location','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params, 'Job/sort', 'required');
		$message .= $Valid->ErrorMsg($params,'Job/sort','mbmaxlength','2000');
		$message .= $Valid->ErrorMsg($params,'Job/message','mbmaxlength','2000');
		if(!empty($params['Job']['hope_language_note'])){
			$message .= $Valid->ErrorMsg($params,'Job/hope_language_note','mbmaxlength','30');
		}
		$message .= $Valid->ErrorMsg($params, 'Job/email3', 'email');
		return $message;
	}

	function createCondition($search, $params) {

		$lang_other = false;
		if (!empty ($params['JobInfo']['hope_language_id'])) {
			foreach ($params['JobInfo']['hope_language_id'] as $key => $val) {
				if ($val == '99') {
					$lang_other = true;
					break;
				}
			} //歓迎するネイティブ言語その他
		}
		if (!empty ($params['JobInfo']['post_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_post_inner', 'post_id');
			if (!empty ($ids)) {
				$params['JobInfo']['post_id'] = $ids;
			}
		}
		if (!empty ($params['JobInfo']['hope_language_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_hope_lang_inner', 'hope_language_id');
			if (!empty ($ids)) {
				$params['JobInfo']['lang_id'] = $ids;
			}
		}
		if (!empty ($params['JobInfo']['hope_member_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_hope_member_inner', 'hope_member_id');
			if (!empty ($ids)) {
				$params['JobInfo']['member_id'] = $ids;
			}
		}
		if (!empty ($params['JobInfo']['attention_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_attention_inner', 'attention_id');
			if (!empty ($ids)) {
				$params['JobInfo']['attention_id'] = $ids;
			}
		}
		if (!empty ($params['JobInfo']['job_area_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_area_inner', 'job_area_id');
			if (!empty ($ids)) {
				$params['JobInfo']['area_id'] = $ids;
			}
		}
		if (!empty ($params['JobInfo']['category_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_category_inner', 'category_id');
			if (!empty ($ids)) {
				$params['JobInfo']['category_id'] = $ids;
			}
		}

		$ids = $this->getDeleteCompanyId();
		if (!empty ($ids)) {
			$params['JobInfo']['delete_id'] = $ids;
		}

		$sql = "Job.deleted is null" .
		" AND Job.id in (###post_id###)" .
		" AND Job.id in (###member_id###)" .
		" AND Job.id in (###attention_id###)" .
		" AND Job.id in (###area_id###)" .
		" AND Job.id in (###category_id###)" .
		" AND Job.company_id not in (###delete_id###)" .
		" AND not(Job.public_end < '###public_start###' or Job.public_start > '###public_end###')";
		if ($lang_other) {
			$sql .= " AND (Job.id in (###lang_id###) OR Job.hope_language_note like '%###hope_language_other###%')";
		} else {
			$sql .= " AND Job.id in (###lang_id###)";
		}

		return $search->calulate($sql, $params['JobInfo']);

	}

	function createCondition2($search, $params) {
		if (!empty ($params['JobArea']['job_area_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_area_inner', 'job_area_id');
			if (!empty ($ids)) {
				$params['JobInfo']['area_id'] = $ids;
			}
		}
		if (!empty ($params['JobInfo']['category_id'])) {
			$ids = $this->getJobIdBySearch($search, $params, 'job_category_inner', 'category_id');
			if (!empty ($ids)) {
				$params['JobInfo']['category_id'] = $ids;
			}
		}

	}
	function getStatusCondition($search, $status) {
		$ids = $this->getDeleteCompanyId();
		$params = array ();
		if (!empty ($ids)) {
			$params['JobInfo']['delete_id'] = $ids;
		}
		$sql = "Job.deleted is null" .
		" AND Job.company_id not in (###delete_id###)" .
		" AND Job.job_status_id = ###status_id###" .
			" AND ###if_public### (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\") <= to_char(now(),'YYYY-MM-DD HH24:MI')" . //查询掲載中数据
				" and (\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\") >= to_char(now(),'YYYY-MM-DD HH24:MI') and Job.public_close_date is null" .
			" AND ###if_public_stop### ((\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') or Job.public_close_date is not null)" . //查询掲載中止数据
	" AND ###if_public_wait### to_char(now(),'YYYY-MM-DD HH24:MI')<=(\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\")"; //查询掲載待ち数据
		switch ($status) {
			case 'public' : //??掲載中数据
				$params['JobInfo']['status_id'] = '1';
				$params['JobInfo']['if_public'] = ' (1=1) and ';
				break;
			case 'public_stop' : //??掲載中止数据
				$params['JobInfo']['status_id'] = '1';
				$params['JobInfo']['if_public_stop'] = ' (1=1) and ';
				break;
			case 'unapprove' : //??未承認数据
				$params['JobInfo']['status_id'] = '2';
				break;
			case 'public_wait' : //??掲載待ち数据
				$params['JobInfo']['status_id'] = '1';
				$params['JobInfo']['if_public_wait'] = ' (1=1) and ';
				break;
			case 'approve_wait' : //??承認待ち数据
				$params['JobInfo']['status_id'] = ' 0 and (1=1) ';
				break;
		}

		return $search->calulate($sql, $params['JobInfo']);
	}

	function getJobIdBySearch($search, $params, $table, $col) {
		$sql = "SELECT Job.job_id from $table as Job WHERE Job.deleted is null and Job.$col in (###$col###)";
		$sql = $search->calulate($sql, $params['JobInfo']);
		$data = $this->query($sql);
		if (!empty ($data)) {
			$arr = array ();
			foreach ($data as $d) {
				$arr[] = $d['Job']['job_id'];
			}
			return implode(',', $arr);
		} else {
			return -1;
		}
	}

	function getIdsBySearch($search, $params, $table, $col) {

		$sql = $search->calulate($sql, $params['Search']);
		$sql = "SELECT Job.job_id from $table as Job WHERE Job.deleted is null and Job.$col in (###$col###)";
		$data = $this->query($sql);
		if (!empty ($data)) {
			$arr = array ();
			foreach ($data as $d) {
				$arr[] = $d[0]['job_id'];
			}
			return implode(',', $arr);
		} else {
			return -1;
		}
	}
	function getJobIdByCom($search,$params,$table,$col){		
		$sql = "SELECT Job.id from $table as Job WHERE Job.deleted is null and Job.company_id in (###$col###)";		
		$sql = $search->calulate($sql,$params['Search']);
		$data = $this->query($sql);
		if(!empty($data)){
			$arr = array();
			foreach($data as $d){
				//@author wangyuhua 2012/4/5 update start
// 				$arr[] = $d['Job']['id'];
				$arr[] = $d[0]['id'];
				//@author wangyuhua 2012/4/5 update end
			}
			return implode(',',$arr);
		}
		else{
			return -1;
		}
	}
	function getDeleteCompanyId() {
		$sql = "SELECT Company.id from company_info as Company WHERE Company.deleted is not null";
		$data = $this->query($sql);
		if (!empty ($data)) {
			$arr = array ();
			foreach ($data as $d) {
				$arr[] = $d[0]['id'];
			}
			return implode(',', $arr);
		} else {
			return null;
		}
	}

	function getCount($condition = null) {
		$sql = "SELECT" .
		" count(1) as count" .
		" FROM job_mst Job" .
		" JOIN company_info Company" .
		" on Company.deleted is null" .
		" and Company.id = Job.company_id ";
		if (!empty ($condition)) {
			$sql .= " WHERE " . $condition;
		}
		$data = $this->query($sql);
		return $data['0']['0']['count'];
	}

	function deleteJob($id) {
		$sql = "UPDATE job_mst set deleted = now() where id in ($id)";
		$this->query($sql);
	}
	/**
	 * @author lizeqiu
	 * @desc job刊载结束
	 * */
	function closeJob($id) {
		$sql = "UPDATE job_mst set public_close_date = now() where id in ($id)";
		$this->query($sql);
	}
	function processExcelData(& $data) {
		foreach ($data as & $d) {
			$d['Job']['Area_data'] = $this->processData($d, 'JobArea');
			$d['Job']['Category_data'] = $this->processData($d, 'JobCategory');
			$d['Job']['Language_data'] = $this->processData($d, 'JobHopeLanguage');
			$d['Job']['Member_data'] = $this->processData($d, 'JobHopeMember');
			$d['Job']['Post_data'] = $this->processData($d, 'JobPost');
		}
	}

	function processData($data, $key) {
		if (!isset ($data[$key])) {
			return '';
		}
		$arr = array ();
		foreach ($data[$key] as $d) {
			$arr[] = $d['SysParams']['name'];
		}
		return implode(',', $arr);
	}

	function getExcelCol() {
		$col_name = array (
			'Company/company_name' => '会社名',
			'Company/company_alias' => '会社の愛称',
			'Job/id' => '求人ID',
			'Job/title' => '求人タイトル',
			'Job/Category_data' => '職種',
			'Job/Post_data' => '雇用形態',
			'Job/content' => '仕事内容',
			'Job/condition' => '必要資格',
			'Job/salary' => '給与・待遇・ビザサポート',
			'Job/start_date' => '勤務開始日',
			'Job/location' => '勤務地',
			'Job/sort'=>'応募手順',
	        'Job/message' => '求職者へのメッセージ',
			'Job/Language_data' => '歓迎するネイティブ言語',
			'Job/hope_language_note' => 'その他',
			'Job/Member_data' => '歓迎する求職者属性',
			//'Job/id'=>'求人の注目情報',
	        'Job/Area_data' => '求人の登録地域',
			'Job/Category_data' => '求人の求人カテゴリ',
			//'Job/id'=>'写真',
	        'Job/created' => '投稿日',
	        'Job/modified' => '投稿内容修正日'
			// 'Job/'=>'投稿掲載日　',
		    // 'Job/id'=>'掲載期間',
		    // 'Job/id'=>'掲載終了日',
		    // 'Job/id'=>'検索結果に表示しない',
		);
		return $col_name;
	}

	function createMailCondition($search, $params) {

		$lang_other = false;
		if (!empty ($params['MailMaga']['hope_language_id'])) {
			foreach ($params['MailMaga']['hope_language_id'] as $key => $val) {
				if ($val == '99') {
					$lang_other = true;
					break;
				}
			} //歓迎するネイティブ言語その他
		}
		if (!empty ($params['MailMaga']['post_id'])) {
			$ids = $this->getMailIdBySearch($search, $params, 'job_post_inner', 'post_id');
			if (!empty ($ids)) {
				$params['MailMaga']['post_id'] = $ids;
			}
		}
		if (!empty ($params['MailMaga']['hope_language_id'])) {
			$ids = $this->getMailIdBySearch($search, $params, 'job_hope_lang_inner', 'hope_language_id');
			if (!empty ($ids)) {
				$params['MailMaga']['lang_id'] = $ids;
			}
		}
		if (!empty ($params['MailMaga']['hope_member_id'])) {
			$ids = $this->getMailIdBySearch($search, $params, 'job_hope_member_inner', 'hope_member_id');
			if (!empty ($ids)) {
				$params['MailMaga']['member_id'] = $ids;
			}
		}
		//		if(!empty($params['JobInfo']['attention_id'])){
		//			$ids = $this->getJobIdBySearch($search,$params,'job_hope_member_inner','attention_id');
		//			if(!empty($ids)){
		//				$params['JobInfo']['attention_id'] = $ids;
		//			}
		//		}
		if (!empty ($params['MailMaga']['job_area_id'])) {
			$ids = $this->getMailIdBySearch($search, $params, 'job_area_inner', 'job_area_id');
			if (!empty ($ids)) {
				$params['MailMaga']['area_id'] = $ids;
			}
		}
		if (!empty ($params['MailMaga']['category_id'])) {
			$ids = $this->getMailIdBySearch($search, $params, 'job_category_inner', 'category_id');
			if (!empty ($ids)) {
				$params['MailMaga']['category_id'] = $ids;
			}
		}

		$ids = $this->getDeleteCompanyId();
		if (!empty ($ids)) {
			$params['MailMaga']['delete_id'] = $ids;
		}

		$sql = "Job.deleted is null" .
		" AND Job.id in (###post_id###)" .
		" AND Job.id in (###member_id###)" .
		" AND Job.id in (###attention_id###)" .
		" AND Job.id in (###area_id###)" .
		" AND Job.id in (###category_id###)" .
		" AND Job.company_id not in (###delete_id###)" .
		" AND not(Job.public_end < '###public_start###' or Job.public_start > '###public_end###')" .
		" AND not(Job.pickup_end < '###pickup_start###' or Job.pickup_start > '###pickup_end###')" .
		" AND not(Job.attention_end < '###attention_start###' or Job.attention_start > '###attention_end###')";

		if ($lang_other) {
			$sql .= " AND (Job.id in (###lang_id###) OR Job.hope_language_note like '%###hope_language_other###%')";
		} else {
			$sql .= " AND Job.id in (###lang_id###)";
		}

		return $search->calulate($sql, $params['MailMaga']);

	}

	//替换了pg函数
	function getMailStatusCondition($search, $status) {
		$ids = $this->getDeleteCompanyId();
		$params = array ();
		if (!empty ($ids)) {
			$params['MailMaga']['delete_id'] = $ids;
		}
		$sql = "Job.deleted is null" .
		" AND Job.company_id not in (###delete_id###)" .
		" AND Job.job_status_id = ###status_id###" .
			" AND ###if_public### (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI')" . //查询掲載中数据
	" and Job.public_close_date is null" .
		" and (date_part('epoch' :: TEXT, now())-date_part('epoch' :: TEXT,Job.public_end) < 0)" .
			" AND ###if_public_stop### (\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') or \"Job\".\"public_close_date\" is not null" . //查询掲載中止数据
		" AND ###if_public_wait### to_char(now(),'YYYY-MM-DD HH24:MI')<=(\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")" . //查询掲載待ち数据
		" AND ###if_pickup### Job.pickup_start <= to_char(now(),'YYYY-MM-DD')" . //查询pickup
	" and (date_part('epoch' :: TEXT, now())-date_part('epoch' :: TEXT,Job.pickup_end) < 0)" .
		" AND ###if_attention### Job.attention_start <= to_char(now(),'YYYY-MM-DD')" .
		" and (date_part('epoch' :: TEXT, now())-date_part('epoch' :: TEXT,Job.attention_end) < 0)";

		return $search->calulate($sql, $params['MailMaga']);
	}

	function getMailIdBySearch($search, $params, $table, $col) {
		$sql = "SELECT Job.job_id from $table as Job WHERE Job.deleted is null and Job.$col in (###$col###)";
		$sql = $search->calulate($sql, $params['MailMaga']);
		$data = $this->query($sql);
		if (!empty ($data)) {
			$arr = array ();
			foreach ($data as $d) {
				$arr[] = $d['Job']['job_id'];
			}
			return implode(',', $arr);
		} else {
			return -1;
		}
	}
	function republic($job_id) {
		$sql = "UPDATE job_mst set public_close_date = null where id = " . $job_id;
		$this->query($sql);
	}

	function delLangNote($jobId) {
		$this->execute("UPDATE job_mst set hope_language_note = null where id =$jobId");
	}

	function delEmail3($jobId) {
		$this->execute("UPDATE job_mst set email3 = null where id =$jobId");
	}
	
	function delFieldById($jobId,$field){
		$sql = "UPDATE job_mst set $field = null where id = " . $jobId;
		$this->query($sql);
	}

	function delImgFile($jobId) {
		$this->execute("UPDATE job_mst set img_file = null where id =$jobId");
	}
	
	/*
	 * @author wangyuhua 2012/4/24 add start
	* get attention job basic information 
	*/
	function getAttentionBasic0Job() {
		$condition = "\"Job\".\"job_status_id\"=1 AND \"Job\".\"attention_basic_flg\"=0" .
				" AND to_char(\"Job\".\"attention_start\",'YYYY-MM-DD') <= to_char(now(),'YYYY-MM-DD') AND to_char(\"Job\".\"attention_end\",'YYYY-MM-DD') >= to_char(now(),'YYYY-MM-DD')" .
				" AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||'23:59:59')>=to_char(now(),'YYYY-MM-DD HH24:MI:SS')" .
				" AND \"Job\".\"public_close_date\" is NULL AND \"Job\".\"deleted\" is NULL" .
				" AND \"Job\".\"search_flg\"=0 "
				//lfx added 2013-09-11 start
				." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL)" 
				//lfx added 2013-09-11 end		
				;
		return $this->findAll($condition, '', 'Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', null, null, 2);
	}
	//@author wangyuhua 2012/4/24 add end
	
	/*
	 * add 2008-01-17 xiejun
	 * get attention job information 
	 */
	function getAttentionJob() {
		$condition = "\"Job\".\"job_status_id\"=1 AND \"Job\".\"attention_basic_flg\"=1" .
		" AND to_char(\"Job\".\"attention_start\",'YYYY-MM-DD') <= to_char(now(),'YYYY-MM-DD') AND to_char(\"Job\".\"attention_end\",'YYYY-MM-DD') >= to_char(now(),'YYYY-MM-DD')" .
//		" AND concat(Job.public_start,' ',Job.public_start_hour,':',Job.public_start_min)<=now()AND concat(Job.public_end,' ',Job.public_end_hour,':',Job.public_end_min)>=now()" .		
		" AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||'23:59:59')>=to_char(now(),'YYYY-MM-DD HH24:MI:SS')" .		
		" AND \"Job\".\"public_close_date\" is NULL AND \"Job\".\"deleted\" is NULL" .
		" AND \"Job\".\"search_flg\"=0 "
		//lfx added 2013-09-11 start
		." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL) "
		//lfx added 2013-09-11 end
// 		." AND \"Job\".\"attention_basic_flg\"=1"
		;
		return $this->findAll($condition, '', 'Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', null, null, 2);
	}
	/*
	 * add 2008-01-17 xiejun
	 * get pickup job information 
	 */
	function getPickUpJob($limit = null) {
		$condition = "\"Job\".\"job_status_id\"=1 AND \"Job\".\"pickup_basic_flg\"=1" .
		" AND to_char(\"Job\".\"pickup_start\",'YYYY-MM-DD') <= to_char(now(),'YYYY-MM-DD') AND to_char(\"Job\".\"pickup_end\",'YYYY-MM-DD') >= to_char(now(),'YYYY-MM-DD')" .
//		" AND concat(Job.public_start,' ',Job.public_start_hour,':',Job.public_start_min)<=now()AND concat(Job.public_end,' ',Job.public_end_hour,':',Job.public_end_min)>=now()" .
		" AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||'23:59:59')>=to_char(now(),'YYYY-MM-DD HH24:MI:SS')" .
		" AND \"Job\".\"public_close_date\" is NULL AND \"Job\".\"deleted\" is NULL" .
		" AND \"Job\".\"search_flg\"=0 AND \"Job\".\"pickup_basic_flg\"=1"
		//lfx added 2013-09-11 start
		." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL)" 
		//lfx added 2013-09-11 end		
		;

		//for pickup_img
		$order = 'substring("Job"."pickup_img_file",0,3) desc,"Job"."public_start" desc,"Job"."public_start_hour" desc,"Job"."public_start_min" desc,"Job"."approve_date" desc';
		return $this->getPickUpData($condition,$order,$limit);
//		return $this->findAll($condition, '', 'substring(Job.pickup_img_file,0,3) desc,Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', null, null, 2);
	}
	/**
	* @author zhouguoyan
	* @desc 查找符合Attention信息
	**/
	function getAreaAttentionJob($search,$params) {
		$condition = "to_char(\"Job\".\"attention_start\",'YYYY-MM-DD') <= to_char(now(),'YYYY-MM-DD') AND to_char(\"Job\".\"attention_end\",'YYYY-MM-DD') >= to_char(now(),'YYYY-MM-DD')" .
//		" AND concat(Job.public_start,' ',Job.public_start_hour,':',Job.public_start_min)<=now()AND concat(Job.public_end,' ',Job.public_end_hour,':',Job.public_end_min)>=now()" .
		" AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||'23:59:59')>=to_char(now(),'YYYY-MM-DD HH24:MI:SS')" .
        " AND \"Job\".\"deleted\" is NULL" . 
		" AND \"Job\".\"public_close_date\" is NULL" . 
		" AND \"Job\".\"id\" in (###Attention_info_id###)" 
		//lfx added 2013-09-11 start
		." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL)" 
		//lfx added 2013-09-11 end		
		;
		$condition = $search->calulate($condition,$params);
		return $this->findAll($condition, '', 'Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', null, null, 2);
	}
	/**
	* @author zhouguoyan
	* @desc 查找符合PickUp信息
	**/
	function getAreaPickUpJob($search,$params) {
		$condition = "to_char(\"Job\".\"pickup_start\",'YYYY-MM-DD') <= to_char(now(),'YYYY-MM-DD') AND to_char(\"Job\".\"pickup_end\",'YYYY-MM-DD') >= to_char(now(),'YYYY-MM-DD')" .
//        " AND concat(Job.public_start,' ',Job.public_start_hour,':',Job.public_start_min)<=now()AND concat(Job.public_end,' ',Job.public_end_hour,':',Job.public_end_min)>=now()" .		
        " AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\")<=to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||'23:59:59')>=to_char(now(),'YYYY-MM-DD HH24:MI:SS')" .		
        " AND \"Job\".\"deleted\" is NULL" . 
		" AND \"Job\".\"public_close_date\" is NULL" . 
		" AND \"Job\".\"id\" in (###Attention_info_id###)" 
		//lfx added 2013-09-11 start
		." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL)" 
		//lfx added 2013-09-11 end
		;
		$condition = $search->calulate($condition,$params);
		
		$order = 'substring(Job.pickup_img_file,0,3) desc,Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc';
		
		//for pickup_img
		return $this->getPickUpData($condition,$order);
//		return $this->findAll($condition, '', 'substring(Job.pickup_img_file,0,3) desc,Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', null, null, 2);
	}
	/**
	* @author zhouguoyan
	* @desc 查找符合一般信息
	**/
	function getAreaSampleJob($search,$params,$show=20,$page=1) {
		$condition = "\"Job\".\"job_status_id\"=1 AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\") <= to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\") >= to_char(now(),'YYYY-MM-DD HH24:MI')" .
					" AND \"Job\".\"public_close_date\" is NULL" .
					" AND \"Job\".\"deleted\" is NULL AND \"Job\".\"id\" IN (###sample_info_id###) "
					//lfx added 2013-09-11 start
					." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL)" 
					//lfx added 2013-09-11 end
					;
		$condition = $search->calulate($condition,$params);
		return $this->findAll($condition, '', 'Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', $show, $page, 2);
	}
	
	function getAreaSampleJobCount($search,$params) {
		$condition = "\"Job\".\"job_status_id\"=1 AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\") <= to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\") >= to_char(now(),'YYYY-MM-DD HH24:MI')" .
					" AND \"Job\".\"public_close_date\" is NULL" .
					" AND \"Job\".\"deleted\" is NULL AND \"Job\".\"id\" IN (###sample_info_id###) "
					//lfx added 2013-09-11 start
					." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL) "
					//lfx added 2013-09-11 end
					;
		$condition = $search->calulate($condition,$params);
		return $this->findCount($condition);
	}
	/*
	 * add 2008-01-17 xiejun
	 * get sample job information 
	 */

	function getPublicJob($show=30,$page=1) {
		$condition = "\"Job\".\"job_status_id\"=1 AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\") <= to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\") >= to_char(now(),'YYYY-MM-DD HH24:MI')" .
		" AND \"Job\".\"public_close_date\" is NULL AND \"Job\".\"deleted\" is NULL"
				//lfx added 2013-09-11 start
				." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL) "
				//lfx added 2013-09-11 end
				;
		return $this->findAll($condition, '', 'Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', $show, $page, 2);
	}
	
	function getPublicJobCount() {
		$condition = "\"Job\".\"job_status_id\"=1 AND (\"Job\".\"public_start\"||' '||\"Job\".\"public_start_hour\"||':'||\"Job\".\"public_start_min\") <= to_char(now(),'YYYY-MM-DD HH24:MI') AND (\"Job\".\"public_end\"||' '||\"Job\".\"public_end_hour\"||':'||\"Job\".\"public_end_min\") >= to_char(now(),'YYYY-MM-DD HH24:MI')" .
		" AND \"Job\".\"public_close_date\" is NULL AND \"Job\".\"deleted\" is NULL"
		//lfx added 2013-09-11 start
		." AND  (\"Job\".\"cn_asia\"<>'1'  or \"Job\".\"cn_asia\" is NULL)" 
		//lfx added 2013-09-11 end				
		;
		return $this->findCount($condition);
	}
	
	function refreshEmail1($email1,$company_id){
		$this->execute("UPDATE job_mst set email1 = '$email1' where company_id =$company_id and email1 is not null");
	}
	
	function refreshEmail2($email2,$company_id){
		$this->execute("UPDATE job_mst set email2 = '$email2' where company_id =$company_id and email2 is not null");
	}
	
		function getPickUpData($condition,$order,$limit=null){
//		$sql = "SELECT \"Job\".*,substring(\"Job\".\"pickup_img_file\", 0, 3) as \"pic\",\"Company\".\"company_name\",\"Company\".\"company_alias\" " .
//			   "FROM \"job_mst\" AS \"Job\" " .
//			   "LEFT JOIN \"company_info\" AS \"Company\" ON \"Job\".\"company_id\" = \"Company\".\"id\"".
//		       "LEFT JOIN \"company_account_mst\" AS \"CompanyAccount\" ON \"Job\".\"admin_id\" = \"CompanyAccount\".\"id\"";
//		$sql.= " WHERE " . $condition;	
//		$sql.= " ORDER BY " . $order;
//		return $this->query($sql);
		return $this->findAll($condition, '', 'Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc', $limit, null, 2);
		
	}
	
	function getJobList($tameid = null,$areaid=null,$attid=null,$hmid=null,$page = 1,$temas,$lang_data,$memb_type,$attention_data,$pagesize=20)
	{
		$fileds = ' Job.title,cinfo.company_name,Job.work_type,Job.content,Job.id as job_id,Job.pickup_img_file';
		$select = "select ".$fileds." from job_mst as   Job  join company_info as  cinfo on (cinfo.id=Job.company_id and cinfo.deleted is null)";
		$where .="where Job.deleted is null and Job.search_flg=0 and Job.job_status_id  = 1 and (Job.public_start||' '||Job.public_start_hour||':'||Job.public_start_min) <= to_char(now(),'YYYY-MM-DD HH24:MI')";
		$where .="and (Job.public_end||' '||Job.public_end_hour||':'||Job.public_end_min)>=to_char(now(),'YYYY-MM-DD HH24:MI') ";
		$where .=" and (Job.public_close_date is null) and (Job.cn_asia<>'1' or Job.cn_asia is null)";
		

		if($tameid)
		{
			$join = " join job_category_inner as cinner on (cinner.job_id = Job.id and cinner.deleted is null)";
			$where .=" and category_id=".$tameid;
		}
		if($areaid)
		{
			$join .=" join job_area_inner as ainner on (ainner.job_id = Job.id and ainner.deleted is null) ";
			if($areaid == 9397)
				$where .=" and job_area_id in (93,94,96,97)";
			else
				$where .=" and job_area_id=".$areaid;
		}
		if($attid)
		{
			$join =" join job_attention_inner as jainner on (jainner.job_id = Job.id and jainner.deleted is null) ";
			$where .=" and attention_id=".$attid;
		}
		if($hmid)
		{
			$join =" join job_hope_member_inner as hminner on (hminner.job_id = Job.id and hminner.deleted is null) ";
			$where .=" and hope_member_id=".$hmid;
		}
		$order = " order by Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc";
		$selectcount = "select count(*) as count from job_mst as   Job  join company_info as  cinfo on (cinfo.id=Job.company_id and cinfo.deleted is null)";
		
		$sqlcount = $selectcount.$join.$where;
		$datacount = $this->query($sqlcount);
		$totalnum = isset($datacount[0][0]['count'])?$datacount[0][0]['count']:0;
		if($totalnum>0)
		{
			$totalpages = ceil($totalnum/$pagesize);
			$page = $page<1?1:$page;
			$page = $page>$totalpages?$totalpages:$page;
			$s = ($page-1)*$pagesize;
			$e = $page*$pagesize;
			$e = $e>$totalnum?$totalnum:$e;
			$ss = $s>0?$s:1;
			$limit = ' offset '.$s.' limit '.$pagesize;
		}
		$sql = $select.$join.$where.$order.$limit;

		$data = $this->query($sql);
		$jobList = array();

		foreach($data as $k=>$v)
		{
			$job = $v[0];
			$job['Lan'] = $this->getSelectByJobId($job['job_id'],'hope_language_id','job_hope_lang_inner', $lang_data);
			$job['Mem'] = $this->getSelectByJobId($job['job_id'],'hope_member_id','job_hope_member_inner', $memb_type);
			$job['Att'] = $this->getSelectByJobId($job['job_id'],'attention_id','job_attention_inner', $attention_data);
			$job['Cname'] = $this->getSelectByJobId($job['job_id'],'category_id','job_category_inner', $temas);
			$jobList[] = $job;
		}
		//分页
		$curpage = 1;$nextpage = $totalpages;$npages = $page+2;
		if($page!=$totalpages)
		{
			$nextpage = $page+1;
		}
		
		if($page>1){
			$curpage = $page-1;
		}
		if($page+5>$totalpages){
			$npages = $totalpages;
		}else if($page<5 && $totalpages>=5)
		{
			$npages = 5;
		}else if($totalpages<5)
		{
			$npages = $totalpages;
		}
		$urlarr = array('tameid'=>$tameid,'areaid'=>$areaid,'attid'=>$attid,'hmid'=>$hmid);
		$url = http_build_query($urlarr);
		$result = array('data'=>$jobList,'totalpage'=>$totalpages,'curpage'=>$curpage,'nextpage'=>$nextpage,
						'totalnum'=>$totalnum,'npages'=>$npages,'s'=>$ss,'e'=>$e,'url'=>$url);
		return $result;
	}
	
	
	function getJobByCompanyID($company_id,$pagesize=3)
	{
		$fileds = ' Job.title,Job.work_type,Job.content,Job.id as job_id';
		$select = "select ".$fileds." from job_mst as Job ";
		$where .="where Job.deleted is null and Job.search_flg=0 and Job.job_status_id  = 1 and (Job.public_start||' '||Job.public_start_hour||':'||Job.public_start_min) <= to_char(now(),'YYYY-MM-DD HH24:MI')";
		$where .="and (Job.public_end||' '||Job.public_end_hour||':'||Job.public_end_min)>=to_char(now(),'YYYY-MM-DD HH24:MI') ";
		$where .=" and (Job.public_close_date is null) and (Job.cn_asia<>'1' or Job.cn_asia is null)";

		$order = " order by Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc";
		$limit = " limit ".$pagesize;
		$sql = $select.$where.$order.$limit;

		$data = $this->query($sql);
		$jobList = array();
	
		foreach($data as $k=>$v)
		{
			$job = $v[0];
			
			$jobList[] = $job;
		}
		return $jobList;
	}
	
	function getSelectByJobId($id,$select,$tablename,$sourcedata) {
		$sql = "SELECT $select from $tablename  WHERE deleted is null and job_id=".$id;
		$data = $this->query($sql);
		if (!empty ($data)) {
			$arr = $arrName = array(); 
			foreach ($data as $d) {
				$lid = $d[0][$select];
				$name = $sourcedata[$lid]?$sourcedata[$lid]:"";
				$arr[] = $name;
			}
			$str = implode("、", $arr);
			return $str;
		} else {
			return ;
		}
	}
	
	
	function getJobListByAreaId($areaid=null,$pagesize=5)
	{
		$where='';
		$fileds = ' Job.title,cinfo.company_name,Job.work_type,Job.content,Job.id as job_id,Job.location,Job.pickup_img_file';
		$select = "select ".$fileds." from job_mst as   Job  join company_info as  cinfo on (cinfo.id=Job.company_id and cinfo.deleted is null)";
		$where .="where Job.deleted is null and Job.search_flg=0 and Job.job_status_id  = 1 and (Job.public_start||' '||Job.public_start_hour||':'||Job.public_start_min) <= to_char(now(),'YYYY-MM-DD HH24:MI')";
		$where .="and (Job.public_end||' '||Job.public_end_hour||':'||Job.public_end_min)>=to_char(now(),'YYYY-MM-DD HH24:MI') ";
		$where .=" and (Job.public_close_date is null) and (Job.cn_asia<>'1' or Job.cn_asia is null)";
		
		if($areaid)
		{
			$join .=" join job_area_inner as ainner on (ainner.job_id = Job.id and ainner.deleted is null) ";
			$where .=" and job_area_id=".$areaid;
		}
		$order = " order by Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc";
		$limit = ' offset 0 limit '.$pagesize;
		$sql = $select.$join.$where.$order.$limit;

		$data = $this->query($sql);
		$jobList = array();
		foreach($data as $k=>$v)
		{
			$job = $v[0];
			$jobList[] = $job;
		}
		return $jobList;
	}
	
	function getMobilePickUpJob($pagesize=5,$limit = 3)
	{
		$where='';
		$fileds = ' Job.title,cinfo.company_name,Job.work_type,Job.content,Job.id as job_id,Job.location,Job.pickup_img_file';
		$select = "select ".$fileds." from job_mst as   Job  join company_info as  cinfo on (cinfo.id=Job.company_id and cinfo.deleted is null)";
		$where .="where Job.deleted is null and Job.search_flg=0  and (Job.public_start||' '||Job.public_start_hour||':'||Job.public_start_min) <= to_char(now(),'YYYY-MM-DD HH24:MI')";
		$where .="and (Job.public_end||' '||Job.public_end_hour||':'||Job.public_end_min)>=to_char(now(),'YYYY-MM-DD HH24:MI') ";
		$where .=" and (Job.public_close_date is null) and (Job.cn_asia<>'1' or Job.cn_asia is null) and Job.pickup_basic_flg = 1 and Job.public_close_date is null";
		$where .=" and Job.pickup_basic_flg = 1 and Job.pickup_img_file!=''";
		

		$order = " order by substring(Job.pickup_img_file,0,3) desc,Job.public_start desc,Job.public_start_hour desc,Job.public_start_min desc,Job.approve_date desc";
		$limit = ' offset 0 limit '.$pagesize;
		$sql = $select.$where.$order.$limit;
		$data = $this->query($sql);
		$jobList = array();
		foreach($data as $k=>$v)
		{
			$job = $v[0];
			$jobList[] = $job;
		}
		return $jobList;
	}
}
?>
